package com.example.demo.mapper;

import com.example.demo.entity.QuestionsOptions;
import com.example.demo.entity.TestQuestions;
import org.apache.ibatis.annotations.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Mapper
public interface QuestionsMapper {
    /**
     * 查询章节试题
     * */
//    @Select("SELECT tq.* FROM test_questions AS tq WHERE tq.`test_questions_id` = ANY(SELECT pq.`test_questions_id` FROM paper_question AS pq WHERE pq.`test_paper_id` = (SELECT tp.`test_paper_id` FROM test_paper AS tp WHERE tp.`chapter_id`=#{chapter_id}))")
    @Select("SELECT tq.* FROM test_questions AS tq WHERE tq.`test_questions_id` = ANY(SELECT cq.`test_questions_id` FROM chapter_questions AS cq WHERE cq.`chapter_id` = #{chapter_id})")
    public List<TestQuestions> selsectlist(Integer chapter_id);

    /**
     * 查询试卷试题
     */
    @Select("SELECT tq.* FROM test_questions AS tq WHERE tq.`test_questions_id` = #{test_questions_id}")
    public TestQuestions selsect(Integer test_questions_id);
    @Select("SELECT * FROM questions_options AS qo WHERE qo.`test_questions_id` = #{test_questions_id}")
    public List<QuestionsOptions> selopts(Integer test_questions_id);

    /**
     * 查询试卷题目
     * */
    @Select("SELECT test_paper_name FROM test_paper WHERE chapter_id = #{cid}")
    public String seltpque(Integer cid);//找到试卷名称
//    @Select("SELECT test_paper_id FROM test_paper WHERE chapter_id = #{cid}")
//    public int seltpqueid(Integer cid);//找到试卷ID
//    @Select("SELECT test_questions_id FROM paper_question WHERE test_paper_id = #{tpid}")
//    public List<Integer> selpptid(Integer tpid);//找到章节对应的题目ID
    @Select("SELECT test_questions_id FROM chapter_questions WHERE chapter_id = #{cid}")
    public List<Integer> selpptid(Integer cid);//找到章节对应的题目ID
    @Select("SELECT test_questions_name FROM test_questions WHERE test_questions_id = #{tqid}")
    public String seltqname(Integer tqid);//找到试题名称
    @Select("SELECT * FROM questions_options WHERE test_questions_id = #{tqid}")
    public List<QuestionsOptions> selqops(Integer tqid);//找到题目选项

    /**
     * 查询所有试题
     * */
    @Select("select * from test_questions limit #{page},#{limit}")
    public List<TestQuestions> selquestion(Integer page,Integer limit);//查询出所有题目（分页)
    @Select("select * from test_questions")
    public List<TestQuestions> selquestion1();//查询出所有题目
    @Select("SELECT COUNT(*) FROM test_questions")
    public int selquenums();
    //查询试题答案
    @Select("select * from questions_options where test_questions_id=#{test_questions_id}")
    public List<QuestionsOptions> selqueopt(Integer test_questions_id);//根据题目ID查询答案
    /**
     * 模糊查询
     */
//    @Select("SELECT * FROM test_questions WHERE test_questions_name LIKE concat('%',#{qname},'%') AND test_questions_picture = #{tqp} AND test_questions_history = #{tqh}")
    @Select("<script>" +
            "SELECT * FROM test_questions WHERE 1=1"+
            "<if test='test_questions_name!=null'> and test_questions_name LIKE concat('%',#{param1},'%')</if>"+
            "<if test='test_questions_picture!=null'> and test_questions_picture=#{param2}</if>"+
            "<if test='test_questions_history!=null'> and test_questions_history=#{param3}</if>"+
            "</script>")
    public List<TestQuestions> selmuhu(String test_questions_name,Integer test_questions_picture,Integer test_questions_history);

    /**
     * 查询试题ID
     * */
    @Select("select test_questions_id from test_questions where test_questions_name=#{name}")
    public int selque(String name);
    /**
     * 增加试题
     * */
    @Insert("insert into test_questions values(default,#{test_questions_name},#{test_questions_difficulty},#{test_questions_language},#{test_questions_picture},#{test_questions_history},#{test_questions_score})")
    public int insertquestion(String test_questions_name,Integer test_questions_difficulty,Integer test_questions_language,Integer test_questions_picture,Integer test_questions_history,Integer test_questions_score);
    //增加答案
    @Insert("insert into questions_options values(default,#{test_questions_id},#{questions_options_content},#{questions_options_type})")
    public int inserqueopt(Integer test_questions_id,String questions_options_content,Integer questions_options_type);

    /**
     * 增加章节试题
     * */
    @Insert("insert into paper_question values(default,test_paper_id=#{test_paper_id},test_questions_id=#{test_questions_id})")
    public int insertpaperques(Integer test_paper_id,Integer test_questions_id);

    /**
     * 增加试卷试题
     * */
    @Insert("insert into chapter_questions values(default,#{cid},#{tqid})")
    public int inspques(Integer cid,Integer tqid);

    /**
     * 查询试卷中是否有相同的题
     * */
    @Select("select count(*) from chapter_questions where chapter_id=#{cid} and test_questions_id=#{tqid}")
    public int selspques(Integer cid,Integer tqid);

    /**
     * 更改试题
     * */
    @Update("update test_questions set test_questions_name=#{test_questions_name},test_questions_difficulty=#{test_questions_difficulty},test_questions_picture=#{test_questions_picture},test_questions_history=#{test_questions_history},test_questions_score=#{test_questions_score} where test_questions_id=#{test_questions_id}")
    public int updatetestques(String test_questions_name,Integer test_questions_difficulty,Integer test_questions_picture,Integer test_questions_history,Integer test_questions_score,Integer test_questions_id);

    /**
     * 删除试题
     * */
    @Delete("delete from test_questions where test_questions_id=#{test_questions_id}")
    public int deleteques(Integer test_questions_id);//删除试题
    @Delete("delete from chapter_questions where test_questions_id=#{test_questions_id}")
    int delcques(Integer test_questions_id);//删除章节试题
    @Delete("delete from question_picture where test_questions_id=#{test_questions_id}")
    int delpicques(Integer test_questions_id);//删除图片
    @Select("select count(*) from question_picture where test_questions_id=#{test_questions_id}")
    int selpicques(Integer test_questions_id);//查询图片

    //删除答案
    @Delete("delete from questions_options where test_questions_id=#{test_questions_id}")
    public int delqueopt(Integer test_questions_id);
    //查询在章节中是否存在当前试题
    @Select("select count(*) from chapter_questions where test_questions_id = #{test_questions_id}")
    int selcques(Integer test_questions_id);

    /**
     * 删除章节试题
     * */
    @Delete("delete from paper_question where test_paper_id=(select test_paper_id from test_paper where chapter_id=#{chapter_id}) AND paper_question_id=#{paper_question_id}")
    public int delsecques(Integer chapter_id,Integer paper_question_id);

    /**
     * 上传试题图片
     * */
        @Insert("insert into question_picture values(default,#{question_picture_name},#{question_picture_address},#{test_questions_id})")
    public int intpic(String question_picture_name,String question_picture_address,Integer test_questions_id);

    /**
     * 查询答案是否正确
     * */
    @Select("select questions_options_type from questions_options where questions_options_id = #{questions_options_id}")
    public int seloptty(Integer questions_options_id);

    /**
     * 更改试题选项
     */
    @Update("UPDATE questions_options SET questions_options_type = 0 WHERE test_questions_id = #{qoid}")
    public int upoptionall(Integer qoid);
    @Update("UPDATE  questions_options SET questions_options_content=#{qocent},questions_options_type = #{qotype} WHERE questions_options_id  = #{qoid} AND test_questions_id = #{tqid}")
    public int upoption(String qocent,Integer qotype,Integer qoid,Integer tqid);

    /**
     * 增加章节试题
     */
    @Select("SELECT COUNT(*) FROM chapter_questions WHERE chapter_id = #{cid} AND test_questions_id = #{qid}")
    int selcqion(Integer cid,Integer qid);//查询试题是否重复
    @Insert("insert into chapter_questions values(default,#{cid},#{qid})")
    int inscqion(Integer cid,Integer qid);//增加章节试题

    /**
     * 查询章节试题
     */
    @Select("SELECT test_questions_id FROM chapter_questions WHERE chapter_id = #{cid}")
    List<Integer> selcqus(Integer cid);//查询试题ID
    @Select("SELECT * FROM test_questions WHERE test_questions_id = #{tqid}")
    List<TestQuestions> seltqnm(Integer tqid);//查询试题题目

    /**
     * 查询试题图片
     * @param tqid
     * @return
     */
    @Select("select question_picture_address,test_questions_id from question_picture where test_questions_id = #{tqid}")
    List<HashMap> selqucpic(Integer tqid);



}
